package com.gufe.mapper.admin.datascreen;

import com.gufe.entity.RightCenterDataMain;
import com.gufe.vo.datascreen.RightBottomDataVO;
import com.gufe.vo.datascreen.RightBottomMainDataVO;
import com.gufe.vo.datascreen.RightCenterDataVO;
import com.gufe.vo.datascreen.RightTopDataVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface RightChartMapper {

    // 获取数据大屏右上角数据
    @Select("SELECT college, YEAR(join_army_date) AS enlistmentDate, " +
            "  COUNT(*) AS nums FROM army_member " +
            " WHERE join_army_date >= DATE_SUB('2023-12-31', INTERVAL 8 YEAR) AND join_army_date <= '2023-12-31' " +
            "GROUP BY college, YEAR(join_army_date) ORDER BY YEAR(join_army_date), college;")
    List<RightTopDataVO> getRightTopData();

    // 获取数据大屏右上角大图数据
    @Select("SELECT college, YEAR(join_army_date) AS enlistmentDate, " +
            "  COUNT(*) AS nums FROM army_member " +
            "WHERE join_army_date >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR) " +
            "GROUP BY college, YEAR(join_army_date) ORDER BY  YEAR(join_army_date), college;")
    List<RightTopDataVO> getRightTopMainData();

    @Select("SELECT height, weight FROM precollect_members WHERE gender = #{gender}")
    List<RightCenterDataVO> getRightCenterData(Integer gender);

    /**
     * 获取数据大屏右下角数据
     */
    @Select("SELECT YEAR(join_army_date) AS year,  COUNT(*) AS enlistment, COUNT(troop_awards) AS award,  COUNT(retention_army_status) AS retention " +
            "FROM army_member WHERE join_army_date >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)  GROUP BY YEAR(join_army_date) ORDER BY YEAR(join_army_date) ;")
    List<RightBottomDataVO> getRightBottomData();

    // 获取数据大屏右下角大图数据
    @Select("select name,college,join_army_date as enlistmentTime,troop_awards as award from army_member where troop_awards is not null ")
    List<RightBottomMainDataVO> getRightBottomMainData();

    @Select("SELECT" +
            "  CASE" +
            "    WHEN bmi < 17 THEN 'BMI<17'" +
            "    WHEN bmi >= 17 AND bmi < 17.5 THEN '17≤BMI<17.5'" +
            "    WHEN bmi >= 17.5 AND bmi < 24 THEN '17.5≤BMI<24'" +
            "    WHEN bmi >= 24 AND bmi < 30 THEN '24≤BMI<30'" +
            "    WHEN bmi >= 30 THEN 'BMI≥30' " +
            "  END AS scope, " +
            "    CASE" +
            "      WHEN bmi < 17 THEN 1" +
            "      WHEN bmi >= 17 AND bmi < 17.5 THEN 2" +
            "      WHEN bmi >= 17.5 AND bmi < 24 THEN 3" +
            "      WHEN bmi >= 24 AND bmi < 30 THEN 4" +
            "      WHEN bmi >= 30 THEN 5\n" +
            "    END AS sort_order, " +

            "  SUM(CASE WHEN gender = 0 THEN 1 ELSE 0 END) AS female_count, " +
            "  SUM(CASE WHEN gender = 1 THEN 1 ELSE 0 END) AS male_count " +
            "FROM ( " +
            "  SELECT gender, weight / POWER(height / 100, 2) AS bmi " +
            "  FROM precollect_members  WHERE weight IS NOT NULL AND height IS NOT NULL AND height <> 0 " +
            ") AS t " +
            "GROUP BY scope, sort_order ORDER BY sort_order;")
    List<RightCenterDataMain> getRightCenterMainData();


    @Select("SELECT COUNT(*) FROM precollect_members " +
            " WHERE YEAR(join_data) = #{year}")
    Integer getClubMembers(String year);
}
